package connection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Material;
import model.Person;
import model.Resource;

public class ResourceConnection {

   public ArrayList<Material> getAllMaterialsFromDB(){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		ArrayList<Material> oMaterialList = new ArrayList<Material>();
		
		try {
			
			Statement statement = con.createStatement() ;
			
			ResultSet rset = statement.executeQuery("select * from Material");
			
			while (rset.next()) {
				Material oMaterial = new Material();
				oMaterial.setResourceId(rset.getInt(1));
				oMaterial.setName(rset.getString(2));
				oMaterial.setSpecification(rset.getString(3));				
				
				oMaterialList.add(oMaterial);
				}
			rset.close();
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
		
		return oMaterialList;
		
	}
   public ArrayList<Person> getAllPersonFromDB(){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		ArrayList<Person> oPersonList = new ArrayList<Person>();
		
		try {
			
			Statement statement = con.createStatement() ;
			
			ResultSet rset = statement.executeQuery("select * from Person");
			
			while (rset.next()) {
				Person oPerson = new Person();
				oPerson.setResourceId(rset.getInt(1));
				oPerson.setName(rset.getString(2));
				oPerson.setIdentityNumber(rset.getString(3));		
				oPerson.setSalary(rset.getInt(4));
				
				oPersonList.add(oPerson);
				}
			rset.close();
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
		
		return oPersonList;
		
	}
   
   public ArrayList<Resource> getAllResourceFromDB(){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		ArrayList<Resource> oResourceList = new ArrayList<Resource>();
		
		try {
			
			Statement statement = con.createStatement() ;
			
			ResultSet rset = statement.executeQuery("select id,name from Person union all select id,name from Material");
			
			while (rset.next()) {
				Resource oResource = new Resource();
				oResource.setResourceId(rset.getInt(1));
				oResource.setName(rset.getString(2));
				
				oResourceList.add(oResource);
				}
			rset.close();
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
		
		return oResourceList;
		
	}
   
   public ArrayList<Person> getProjectManagersFromDB(){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		ArrayList<Person> oPersonList = new ArrayList<Person>();
		
		try {
			
			Statement statement = con.createStatement() ;
			
			ResultSet rset = statement.executeQuery("select identity from Person,Status,personstatus " +
					                                "where status.id=3 and status.id=personstatus.statusid and"+
					                                " personstatus.personid=person.id");
			
			while (rset.next()) {
				Person oPerson = new Person();
				oPerson.setIdentityNumber(rset.getString(1));
				
				oPersonList.add(oPerson);
				
				}
			rset.close();
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
		
		return oPersonList;
		
	}
   
}
